//
//  DB.m
//  dictionary
//
//  Created by huangchen on 12-1-29.
//  Copyright hxsd.com 2012年. All rights reserved.
//

#import "DB.h"
#import "SQLiteHelper.h"

@implementation DB

static sqlite3 *kDictionaryDatabase;
static SQLiteHelper *kSqlite;

// Static variables for compiled SQL queries. This implementation choice is to be able to share a one time
// compilation of each query across all instances of the class. Each time a query is used, variables may be bound
// to it, it will be "stepped", and then reset for the next usage. When the application begins to terminate,
// a class method will be invoked to "finalize" (delete) the compiled queries - this must happen before the database
// can be closed.

// Finalize (delete) all of the SQLite compiled queries.
+ (void) finalizeStatements
{
	[kSqlite closeDatabase];
}

// 单例
+ (id) singleton
{
	return [[self alloc] init];
}

- (id) init
{
	if ((self=[super init]) ) {
		if (kDictionaryDatabase == nil)
		{
			if (kSqlite == nil) {
				kSqlite = [[SQLiteHelper alloc] init];
			}
			[kSqlite createEditableCopyOfDatabaseIfNeeded];
			[kSqlite initDatabaseConnection];
			
			kDictionaryDatabase = [kSqlite database];
		}
	}
	
	return self;
}

// 获取字的信息
+ (Word *) fetchWord:(NSString *)word
{
    sqlite3_stmt *state;
    Word *wordData = [[Word alloc] init];
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM word WHERE word = \"%@\"", word];
    const char *sql = [querySQL UTF8String];
    if (sqlite3_prepare_v2(kDictionaryDatabase, sql, -1, &state, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kDictionaryDatabase));
    }
    
    if (sqlite3_step(state) == SQLITE_ROW) 
    {
        wordData.wordId = sqlite3_column_int(state, 0);
        wordData.word = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 1)];
        wordData.py = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 2)];
        wordData.wubi = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 3)];
        wordData.radical = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 4)];
        wordData.stroke = sqlite3_column_int(state, 5);
        wordData.pinyin = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 6)];
        wordData.paraphrase = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 7)];
        wordData.detail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 8)];
    }
    else
    {
        wordData.wordId = wordData.stroke = 0;
        wordData.word = wordData.py = wordData.wubi = wordData.radical = wordData.pinyin = wordData.paraphrase = wordData.detail = @"";
    }
    
    //NSLog(@"%@", wordData.paraphrase);
    
    return wordData;
}

+ (NSMutableArray *) fetchWordsByPinyin:(NSString *)pinyin
{
    sqlite3_stmt *state;
    NSMutableArray *words = [[NSMutableArray alloc] init];
    
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM word WHERE py LIKE \"%@%@%@\" ORDER BY strokes", @"%", pinyin, @"%"];
    const char *sql = [querySQL UTF8String];
    if (sqlite3_prepare_v2(kDictionaryDatabase, sql, -1, &state, NULL) != SQLITE_OK) {
         NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kDictionaryDatabase));
    }
    
    while (sqlite3_step(state) == SQLITE_ROW) {
        Word *wordData = [[Word alloc] init];
        
        wordData.wordId = sqlite3_column_int(state, 0);
        wordData.word = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 1)];
        wordData.py = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 2)];
        wordData.wubi = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 3)];
        wordData.radical = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 4)];
        wordData.stroke = sqlite3_column_int(state, 5);
        wordData.pinyin = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 6)];
        wordData.paraphrase = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 7)];
        wordData.detail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 8)];
        
        [words addObject:wordData];
    }
    
    return words;
}

+ (NSMutableArray *) fetchWordsByRadical:(NSString *)radical strokes:(NSMutableArray *)strokes
{
    sqlite3_stmt *state;
    // 此部首的所有字
    NSMutableArray *words = [[NSMutableArray alloc] init];
    // 按笔画分类字
    NSMutableArray *strokeWords = [[NSMutableArray alloc] init];
    
    // 获取此部首的所有字
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM word WHERE radical=\"%@\"", radical];
    const char *sql = [querySQL UTF8String];
    if (sqlite3_prepare_v2(kDictionaryDatabase, sql, -1, &state, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kDictionaryDatabase));
    }
    
    while (sqlite3_step(state) == SQLITE_ROW) {
        Word *wordData = [[Word alloc] init];
        
        wordData.wordId = sqlite3_column_int(state, 0);
        wordData.word = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 1)];
        wordData.py = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 2)];
        wordData.wubi = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 3)];
        wordData.radical = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 4)];
        wordData.stroke = sqlite3_column_int(state, 5);
        wordData.pinyin = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 6)];
        wordData.paraphrase = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 7)];
        wordData.detail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 8)];
        
        [words addObject:wordData];
    }
    
    // 按笔画数分类
    for (NSString *stroke in strokes) {
        // 相同笔画的字
        NSMutableArray *filterWords = [[NSMutableArray alloc] init];
        // 笔画差
        // NSString *strokeDiff = [NSString stringWithFormat:@"%d", stroke.intValue - radicalword.stroke]; 
        for (Word *word in words) {
            if ([stroke intValue] == word.stroke) {
                [filterWords addObject:word];
            }
        }
        NSDictionary *wordDictionary = [NSDictionary dictionaryWithObject:filterWords forKey:@"words"];
        [strokeWords addObject:wordDictionary];
    }
    
    return strokeWords;
}

+ (NSMutableArray *) fetchWordStrokeByRadical:(NSString *)radical
{
    sqlite3_stmt *state;
    NSMutableArray *strokes = [[NSMutableArray alloc] init];
    
    NSString *querySQL = [NSString stringWithFormat:@"SELECT strokes FROM word WHERE radical=\"%@\" GROUP BY strokes ORDER BY strokes", radical];
    const char *sql = [querySQL UTF8String];
    if (sqlite3_prepare_v2(kDictionaryDatabase, sql, -1, &state, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kDictionaryDatabase));
    }
    
    while (sqlite3_step(state) == SQLITE_ROW) {
        NSString *stroke = [NSString stringWithFormat:@"%d", sqlite3_column_int(state, 0)];
        [strokes addObject:stroke];
    }
    
    return strokes;
}

+ (NSMutableArray *) fetchStrokes
{
    sqlite3_stmt *state;
    NSMutableArray *strokes = [[NSMutableArray alloc] init];
    
    const char *sql = "SELECT strokes FROM word GROUP BY strokes";
    if (sqlite3_prepare_v2(kDictionaryDatabase, sql, -1, &state, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kDictionaryDatabase));
    }
    
    while (sqlite3_step(state) == SQLITE_ROW) {
        NSString *stroke = [NSString stringWithFormat:@"%d", sqlite3_column_int(state, 0)];
        [strokes addObject:stroke];
    }
    
    return strokes;
}

+ (NSMutableArray *) fetchWordsByStroke:(NSString *)stroke
{
    sqlite3_stmt *state;
    NSMutableArray *words = [[NSMutableArray alloc] init];
    
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM word WHERE strokes = %@", stroke];
    const char *sql = [querySQL UTF8String];
    if (sqlite3_prepare_v2(kDictionaryDatabase, sql, -1, &state, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kDictionaryDatabase));
    }
    
    while (sqlite3_step(state) == SQLITE_ROW) {
        Word *wordData = [[Word alloc] init];
        
        wordData.wordId = sqlite3_column_int(state, 0);
        wordData.word = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 1)];
        wordData.py = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 2)];
        wordData.wubi = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 3)];
        wordData.radical = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 4)];
        wordData.stroke = sqlite3_column_int(state, 5);
        wordData.pinyin = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 6)];
        wordData.paraphrase = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 7)];
        wordData.detail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(state, 8)];
        
        [words addObject:wordData];
    }
    
    return words;
}
@end
